USE [dbdata]
GO

/****** Object:  Trigger [tr_FixTimeUpd]    Script Date: 03/15/2012 12:14:59 ******/
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[tr_FixTimeUpd]'))
DROP TRIGGER [dbo].[tr_FixTimeUpd]
GO

USE [dbdata]
GO

/****** Object:  Trigger [dbo].[tr_FixTimeUpd]    Script Date: 03/15/2012 12:14:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create TRIGGER [dbo].[tr_FixTimeUpd] ON [dbdata].[dbo].[accounts] 
FOR 
	UPDATE
AS
DECLARE
@Account_Id int,
@Phone varchar(20),
@Code varchar(12),
@SumOld money,
@SumNew money,
@now datetime

set @now=getdate()
create table #ChangeTable (Account_Id int,Phone varchar(20),  Code varchar(12), SumOld money, SumNew money)
insert into #ChangeTable
	select a.Id, p.phone, ac.code, b.Summary, a.Summary from inserted  a 
	join deleted b on a.Id = b.Id and a.categ_id=b.categ_id
	join dbdata.dbo.Phones p on p.uid=a.uid
	join dbdata.dbo.account_categs ac on a.categ_id=ac.id
	where a.Summary != b.Summary and isnull(p.dt_removed, @now+1) >= @Now
	
--select * from #ChangeTable

while exists (select * from #ChangeTable) 
	begin
	select @Account_Id =Account_Id, @Code=Code, @SumOld=SumOld, @SumNew=SumNew, @phone=phone from #ChangeTable
	---exec sms_p.dbo.prints 'AFT 000','Account Fix Time trigger:',@SumNew,32
	update dbdata.dbo.Accounts set dt_Last = @Now where Id = @Account_Id 
	insert dbdata.dbo.SumLog (Account_Id, Phone,  Categ_Code, SumOld, SumNew) values (@Account_id, @Phone, @Code, @SumOld, @SumNew) 
	delete from #ChangeTable where Account_Id = @Account_Id and  code=@Code
end
drop table #ChangeTable

GO

